/********************************************
 * Copyright (c) 2022
 * All rights reserved.
 *
 * 文件名称：data_sq.c
 * 摘 要：数据库操作相关函数
 *
 * 当前版本：1.0
 * 作 者：任慧
 * 完成日期：2022年7月23日
 *
 * ********************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>

#include "sqlite3.h"


//在数据库DB下创建表table
sqlite3 *create_database()
{
	sqlite3       *db = NULL;
	char          *zErrMsg = 0;
	int            rc;
	char           sql[1024];

	rc = sqlite3_open("mqtt.db",&db);
	if (rc)
	{
		printf("Can not open database:%s\n",strerror(errno));
		return NULL;
	}

	memset(sql, 0, sizeof(sql));
	sprintf(sql,"CREATE TABLE IF NOT EXISTS MQTT_DATA("\
			"ID INTEGER PRIMARY KEY AUTOINCREMENT    NOT NULL,"\
			"DATA               CHAR(256)             NOT NULL);");

	rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		printf("SQL error:%s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return NULL;
	}
	return db;
}

//将json格式的数据插入表table
int insert_data(char *p, sqlite3 *db)
{
	char                   *zErrMsg = 0;
	char                    sql[500];
	int                     rc;

	if (!p || !db)
	{
		return -1;
	}

	memset(sql, 0, sizeof(sql));
	sprintf(sql, "INSERT INTO MQTT_DATA (DATA) VALUES('%s');", p);

	/* Execute SQL statement*/
	rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		printf("SQL error:%s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	//printf("Back-up data to table MQTT_DATA\n");

	return 0;
}

//从table中删除id
int delete_data(int id, sqlite3 *db)
{
	char              *zErrMsg = 0;
	int                rc;
	char               sql[50];

	if (id < 0 || !db)
	{
		return -1;
	}

	memset(sql, 0, sizeof(sql));
	sprintf(sql, "DELETE FROM MQTT_DATA WHERE ID=%d;", id);

	/* Execute SQL statement */
	rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	return 0;
}

//查询table中的第一条记录buf，并返回其编号,没有记录返回-1
int selfirst_data(char *buf, sqlite3 *db, int len)
{
	char               *zErrMsg = 0;
	char                sql[500];
	char              **dbResult = NULL;
	int                 nRow;    //查询得到的记录数  此次只能查询一条语句
	int                 nCol;    //列数
	int                 id;      //编号
	int                 rc = -1;

	if (!db || len <= 0)
	{
		return -1;
	}

	memset(sql, 0, sizeof(sql));
	sprintf(sql, "SELECT * FROM MQTT_DATA LIMIT 1;");

	/* Execute SQL statement */
	rc = sqlite3_get_table(db, sql, &dbResult, &nRow, &nCol, &zErrMsg);
	if (nRow == 0)   //没有查询到记录
	{
		return -1;
	}
	printf("nRow=%d\n", nRow);
	if (rc != SQLITE_OK)
	{
		printf("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	printf("Select first data from the table\n");
	id = atoi(dbResult[2]);
	strncpy(buf, dbResult[3], len);

	sqlite3_free_table(dbResult);
	return id;
}


//关闭数据库
int close_database(sqlite3 *db)
{
	if (!db)
	{
		return -1;
	}
	sqlite3_close(db);
	return 0;
}
